package cdu.zch.healthcare;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class Database extends SQLiteOpenHelper {
    //构造函数:用于创建和管理 SQLite 数据库
    public Database(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }
    private static final int DATABASE_VERSION = 3; // 增加版本号

    public Database(Context context) {
        super(context, "healthcare", null, DATABASE_VERSION);
    }

    //oncreate方法在数据库第一次创建时调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        // users表 用户
        String qry1 = "create table users(username text, email text, password text,points int DEFAULT 0)";
        db.execSQL(qry1);

        // cart表 购物车
        String qry2 = "create table cart(username text, product text, price float, otype text)";
        db.execSQL(qry2);

        // order表 订单
        String qry3 = "create table orderplace(username text, fullname text, address text, contactno text, pincode int, date text, time text, amount float, otype text,doctorname TEXT,description TEXT)";
        db.execSQL(qry3);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE users ADD COLUMN points INT DEFAULT 0");
        }
        if(oldVersion<3){
            db.execSQL("ALTER TABLE orderplace ADD COLUMN doctorname TEXT");
            db.execSQL("ALTER TABLE orderplace ADD COLUMN description TEXT");
        }


    }

    /**
     * 注册方法
     * @param username
     * @param email
     * @param password
     */
    public void register(String username, String email, String password) {
        //ContentValues 用于存储列名和对应的数据。
        ContentValues cv = new ContentValues();
        cv.put("username", username);
        cv.put("email", email);
        cv.put("password", password);
        //getWritableDatabase() 获取可写的数据库对象，insert 方法插入数据，close() 关闭数据库连接。
        SQLiteDatabase db = getWritableDatabase();
        db.insert("users", null, cv);
        db.close();
    }

    /**
     * 登陆方法
     * @param username
     * @param password
     * @return
     */
    public int login(String username, String password) {
        int result = 0;
        String[] str = new String[2];
        str[0] = username;
        str[1] = password;
        //getReadableDatabase() 获取可读的数据库对象。
        SQLiteDatabase db = getReadableDatabase();
        //rawQuery 方法执行原始 SQL 查询，Cursor 对象遍历查询结果。
        Cursor c = db.rawQuery("select * from users where username=? and password=?", str);
        if (c.moveToFirst()) {
            result = 1;
        }
        return result;
    }

    //获取用户信息方法
    public ArrayList<String> getUserData(String username) {
        ArrayList<String> userData = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        String[] columns = {"username", "email", "points"};
        String selection = "username=?";
        String[] selectionArgs = {username};
        Cursor cursor = db.query("users", columns, selection, selectionArgs, null, null, null);
        if (cursor.moveToFirst()) {
            userData.add(cursor.getString(0)); // username
            userData.add(cursor.getString(1)); // email
            userData.add(cursor.getString(2)); // points
        }
        cursor.close();
        db.close();
        return userData;
    }

    //加入购物车方法
    public void addCart(String username, String product, float price, String otype) {
        ContentValues cv = new ContentValues();
        cv.put("username", username);
        cv.put("product", product);
        cv.put("price", price);
        cv.put("otype", otype);
        SQLiteDatabase db = getWritableDatabase();
        db.insert("cart", null, cv);
        db.close();
    }

    //查询购物车方法
    public int checkCart(String username, String product) {
        int result = 0;
        String[] str = new String[2];
        str[0] = username;
        str[1] = product;
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.rawQuery("select * from cart where username = ? and product = ?", str);
        if (c.moveToFirst()) {
            result = 1;
        }
        db.close();
        return result;
    }

    //移除购物车商品方法
    public void removeCart(String username, String otype) {
        String[] str = new String[2];
        str[0] = username;
        str[1] = otype;
        SQLiteDatabase db = getWritableDatabase();
        db.delete("cart", "username=? and otype=?", str);
        db.close();
    }


    //获取购物车商品信息
    public ArrayList<String> getCartData(String username, String otype) {
        ArrayList<String> arr = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        String[] str = new String[2];
        str[0] = username;
        str[1] = otype;
        Cursor c = db.rawQuery("select * from cart where username = ? and otype = ?", str);
        if (c.moveToFirst()) {
            do {
                String product = c.getString(1);
                String price = c.getString(2);
                arr.add(product + "$" + price);
            } while (c.moveToNext());
        }
        db.close();
        return arr;
    }


    //添加订单
    public void addOrder(String username, String fullname, String address, String contact, int pincode, String date, String time, float price, String otype, String doctorname, String description) {
        ContentValues cv = new ContentValues();
        cv.put("username", username);
        cv.put("fullname", fullname);
        cv.put("address", address);
        cv.put("contactno", contact);
        cv.put("pincode", pincode);
        cv.put("date", date);
        cv.put("time", time);
        cv.put("amount", price);
        cv.put("otype", otype);
        cv.put("doctorname",doctorname);
        cv.put("description",description);
        SQLiteDatabase db = getWritableDatabase();
        db.insert("orderplace", null, cv);
        db.close();
    }
    public ArrayList<String[]> getAppointmentsAfterDate(String date,String ctime) {
        ArrayList<String[]> data = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        // 定义日期格式
        SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat dbFormat = new SimpleDateFormat("dd/MM/yyyy");
        try{
            // 转换日期格式
            Date inputDate = inputFormat.parse(date);
            String formattedDate = dbFormat.format(inputDate);

            Cursor cursor = db.rawQuery("SELECT * FROM orderplace WHERE date > ? OR (date = ? AND time > ?)", new String[]{formattedDate,formattedDate,ctime});
            if (cursor.moveToFirst()) {
                do {
                    String doctorName = cursor.getString(9);
                    String dateValue = cursor.getString(5);
                    String time = cursor.getString(6);
                    String[] s=new String[]{doctorName,dateValue,time};
                    data.add(s);
                } while (cursor.moveToNext());
            }
            cursor.close();
            db.close();
        } catch (ParseException e) {
            e.printStackTrace();
        }


        return data;
    }

    public ArrayList<String[]> getAppointmentsForDate(String date,String time) {
        ArrayList<String[]> data = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM orderplace WHERE date > ? OR (date = ? AND time > ?)", new String[]{date,date,time});
        if (cursor.moveToFirst()) {
            do {
                String[] appointment = new String[11]; // Assuming there are 9 columns in your orderplace table
                String[] ds=cursor.getString(1).split("：");
                appointment[0] = cursor.getString(0);
                appointment[1] = ds[1];//cursor.getString(1);
                appointment[2] = cursor.getString(2);
                appointment[3] = cursor.getString(3);
                appointment[4] = String.valueOf(4);
                appointment[5] = cursor.getString(5);
                appointment[6] = cursor.getString(6);
                appointment[7] = String.valueOf(7);
                appointment[8] = cursor.getString(8);
                appointment[9] = String.valueOf(9);
                appointment[10] = cursor.getString(10);

                data.add(appointment);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return data;
    }

    public void cancelAppointment(String name,String date) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DELETE FROM orderplace WHERE doctorname = ? and date=?", new String[]{name,date});
        db.close();
    }



    //获取订单信息
    public ArrayList<String> getOrderData(String username) {
        ArrayList<String> arr = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        String[] str = new String[1];
        str[0] = username;
        Cursor c = db.rawQuery("select * from orderplace where username = ?", str);
        if (c.moveToFirst()) {
            do {
                arr.add(c.getString(1) + "$" + // fullname
                        c.getString(2) + "$" + // address
                        c.getString(3) + "$" + // contactno
                        c.getString(4) + "$" + // pincode
                        c.getString(5) + "$" + // date
                        c.getString(6) + "$" + // time
                        c.getString(7) + "$" + // amount
                        c.getString(8) // otype
                );
            } while (c.moveToNext());
        }
        db.close();
        return arr;
    }


    //获取购物车商品信息
    public ArrayList<String> getMedicineOrderData(String username) {
        ArrayList<String> arr = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        String[] str = new String[2];
        str[0] = username;
        str[1] = "medicine";
        Cursor c = db.rawQuery("select * from orderplace where username = ? and otype = ?", str);
        if (c.moveToFirst()) {
            do {
                arr.add(c.getString(1) + "$" + // fullname
                        c.getString(2) + "$" + // address
                        c.getString(3) + "$" + // contactno
                        c.getString(4) + "$" + // pincode
                        c.getString(5) + "$" + // date
                        c.getString(6) + "$" + // time
                        c.getString(7) + "$" + // amount
                        c.getString(8) // otype
                );
            } while (c.moveToNext());
        }
        db.close();
        return arr;
    }

    //查看预约是否存在
    public int checkAppointmentExists(String username, String fullname, String address, String contact, String date, String time) {
        int result = 0;
        String[] str = new String[6];
        str[0] = username;
        str[1] = fullname;
        str[2] = address;
        str[3] = contact;
        str[4] = date;
        str[5] = time;
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.rawQuery("select * from orderplace where username = ? and fullname = ? and address=? and contactno=? and date=? and time=?", str);
        if (c.moveToFirst()) {
            result = 1;
        }
        db.close();
        return result;
    }

}